﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml;
using System.Xml.XPath;
using Microsoft.Office.Interop.Excel;
using System.IO;

namespace workshop3
{
    public partial class Formexport : Form
    {
        List<string> yearlist = new List<string>();
        List<string> semelist = new List<string>();

        System.Data.DataTable courselist = new System.Data.DataTable();
        DataColumn id = new DataColumn("courseid");
        DataColumn coursename = new DataColumn("coursename");

        private void ChangeCourse(string year, string semester)
        {
            bool find = false;
            System.Data.DataTable dt0 = new System.Data.DataTable();
            DataColumn dc3 = new DataColumn("coursename");
            DataColumn dc4 = new DataColumn("courseid");
            dt0.Columns.Add(dc4);
            dt0.Columns.Add(dc3);
            if (File.Exists("course.xml"))
            {
                XmlDocument doc = new XmlDocument();
                doc.Load("course.xml");
                string path = "//course";
                XmlNodeList nodelist = doc.SelectNodes(path);
                if (nodelist.Count != 0)
                {
                    for (int i = 0; i < nodelist.Count; i++)
                    {
                        if (nodelist[i].ChildNodes[1].InnerText == year && nodelist[i].ChildNodes[2].InnerText == semester)
                        {
                            find = true;
                            dt0.Rows.Add(nodelist[i].Attributes[0].Value, nodelist[i].ChildNodes[0].InnerText);
                        }

                    }
                    if (find)
                    {
                        cbcourse.DataSource = dt0;
                        cbcourse.DisplayMember = dt0.Columns[1].ToString();
                        cbcourse.ValueMember = dt0.Columns[0].ToString();
                        cbcourse.SelectedValueChanged += cbcourse_SelectedValueChanged;
                    }


                }
                else
                {
                    MessageBox.Show("Can not find any course!");
                }

                //return dt0;

            }
            //return null;
        }

        public Formexport()
        {
            InitializeComponent();
        }

        private void btnexport_Click(object sender, EventArgs e)
        {
            string localFilePath = "";
            SaveFileDialog fileDialog = new SaveFileDialog();
            fileDialog.Filter = "Excel 表格 (*.xls) | *.xls";
            fileDialog.FilterIndex = 1;
            fileDialog.RestoreDirectory = true;
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                localFilePath = fileDialog.FileName.ToString(); //gain the flie path 
                string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1); //gain the file name from the file path
                
            }
            //return localFilePath;
        

            DataSet test = new DataSet();

            test=LoadXml(cbcourse.SelectedValue.ToString());
            ExportToExcel(localFilePath, test);
        }

        private DataSet LoadXml(string courseid)//load all the xml into dataset and then return it
        {
            XmlDocument doc = new XmlDocument();
            doc.Load("grade.xml");
            String path = "//Grades/grade[@courseid="+courseid+"]";
            XmlNode root = doc.SelectSingleNode(path);
            DataSet ds = new DataSet();
            for(int i = 0; i < root.ChildNodes.Count; i++)
            {
                System.Data.DataTable task = new System.Data.DataTable();
               DataColumn name = new DataColumn("studentname");
                DataColumn id = new DataColumn("studentid");
                DataColumn mark = new DataColumn("mark");
               task.Columns.Add(name);
                task.Columns.Add(id);
                task.Columns.Add(mark);
                XmlNodeList tasklist = root.ChildNodes[i].ChildNodes;
                string taskname = "";
                if (i != root.ChildNodes.Count - 1)
                {
                    taskname = getname(courseid, root.ChildNodes[i].Attributes[0].Value.ToString(), "taskname");
                }
                else
                {
                    taskname = "finalgrade";
                }
                task.TableName = taskname.ToString();
                for (int j = 0; j < tasklist.Count; j++)
                {
                    string studentname=getname(courseid, tasklist[j].Attributes[0].Value.ToString(),"studentname");
                    task.Rows.Add(studentname,tasklist[j].Attributes[0].Value.ToString(), tasklist[j].InnerText);
                }
                ds.Tables.Add(task);
            }
            return ds;
        }

        private string getname(string courseid, string studentid, string type)//this function is to get all the student name and task name from the student xml and course xml
        {
            if (type == "studentname")
            {
                XmlNodeList course = ClassCreateXML.loadxmldata(courseid, "//course", "course.xml", "studentfile");
                //XmlNode studentfile = course[7];
                string filename = course[course.Count-1].InnerText.ToString() + ".xml";
                XmlDocument doc = new XmlDocument();
                doc.Load(filename);
                XmlNodeList namelist = doc.SelectNodes("//student");
                for (int i = 0; i < namelist.Count; i++)
                {
                    if (namelist[i].ChildNodes[0].InnerText.ToString() == studentid)
                    {
                        return namelist[i].ChildNodes[1].InnerText.ToString();
                    }
                }
                
            }
            else if (type == "taskname")
            {
                XmlNodeList course = ClassCreateXML.loadxmldata(courseid, "//course", "course.xml", "task");
                for(int i = 4; i < course.Count - 1; i++)
                {
                    if (course[i].Attributes[0].Value.ToString() == studentid)
                    {
                        return course[i].InnerText.ToString();
                    }
                }

                return "Final mark";//if there is no task id, which is the final grade

            }
            return "unknown";//if do not find, return unknown
        }

    

        private void ExportToExcel(string filename, DataSet ds)
        {
            
            
            if (ds == null)
                return;
            if(File.Exists(filename))
            {
                File.Delete(filename);
            }

            Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            ExcelApp.Application.Workbooks.Add(true);
            ExcelApp.Application.Workbooks.Add(Type.Missing);
            //ExcelApp.Sheets.Delete();
            for(int i= 0; i < ds.Tables.Count; i++)
            {
                System.Data.DataTable dt = ds.Tables[i];
                //Worksheet Sheet1 = new Worksheet();
                //Sheet1.Name = "first task";
                //Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)Sheet1;
                //ExcelApp.Application.Workbooks.Add(sheet);
                //Microsoft.Office.Interop.Excel.Worksheets m_objSheets = (Microsoft.Office.Interop.Excel.Worksheets)ExcelApp.Worksheets;
                //Microsoft.Office.Interop.Excel.Worksheet Sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)(m_objSheets.get_Item(i+1));
                //Microsoft.Office.Interop.Excel.Worksheet Sheets = ExcelApp.
                Microsoft.Office.Interop.Excel.Worksheet Sheet1 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.Sheets.Add();//create new sheet and add it into excel file
                Sheet1.Name = dt.TableName.ToString();//set the sheet name
                var RowAll = Sheet1.get_Range(Sheet1.Cells[2, 1], Sheet1.Cells[(dt.Rows.Count) + 2, 3]);// set the range of the data area in the excel file
                RowAll.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                RowAll.Font.Size = 10;
                RowAll.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

                for (int a = 0; a < dt.Columns.Count; a++)
                {
                    ExcelApp.Cells[2, a + 1] = dt.Columns[a].ColumnName;//set the name of the column 
                }
                for (int a = 0; a < dt.Rows.Count; a++)//start to set the value in each box
                {

                    for (int b = 0; b < dt.Columns.Count; b++)
                    {
                        ExcelApp.Cells[a + 3, b + 1] = dt.Rows[a][b].ToString();

                    }
                }

                ExcelApp.Cells.EntireColumn.AutoFit();
                ExcelApp.Cells.Font.Name = "Georgia";   //text
            }
            ExcelApp.Cells.EntireColumn.AutoFit();
            ExcelApp.Cells.Font.Name = "Georgia";   //text

            ExcelApp.DisplayAlerts = false;
            ExcelApp.ActiveWorkbook.Saved = false;
            ExcelApp.ActiveWorkbook.SaveAs(filename, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel5, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

            ExcelApp.Quit();
            MessageBox.Show("finished");
        }

        private void cbyear_SelectedValueChanged(object sender, EventArgs e)
        {
            cbcourse.DataSource = null;
            ChangeCourse(cbyear.SelectedValue.ToString(), cbsemester.SelectedValue.ToString());
        }

        private void cbsemester_SelectedValueChanged(object sender, EventArgs e)
        {
            cbcourse.DataSource = null;
            ChangeCourse(cbyear.SelectedValue.ToString(), cbsemester.SelectedValue.ToString());
        }

        private void cbcourse_SelectedValueChanged(object sender, EventArgs e)
        {

        }

        private void Formexport_Load(object sender, EventArgs e)
        {
            if (File.Exists("course.xml"))
            {

                //XmlDocument rubdoc = new XmlDocument();
                XmlDocument coursedoc = new XmlDocument();
                coursedoc.Load("course.xml");
                //rubdoc.Load("rubric.xml");
                string coursepath = "//course";
                //string rubpath = "//rubrics/rubric";
                XmlNodeList coursenodelist = coursedoc.SelectNodes(coursepath);
                //XmlNodeList rubnodelist = rubdoc.SelectNodes(rubpath);
                //courselist.Columns.Add(id);
                //courselist.Columns.Add(coursename);
                if (coursenodelist != null)
                {
                    for (int i = 0; i < coursenodelist.Count; i++)
                    {
                        if (yearlist.Count == 0 && semelist.Count == 0)
                        {
                            //courselist.Rows.Add(coursenodelist[i].Attributes[0].Value.ToString(),coursenodelist[i].ChildNodes[0].InnerText);
                            yearlist.Add(coursenodelist[i].ChildNodes[1].InnerText);
                            semelist.Add(coursenodelist[i].ChildNodes[2].InnerText);
                        }
                        else
                        {
                            if (yearlist.Contains(coursenodelist[i].ChildNodes[1].InnerText))
                            {
                                //continue;
                            }
                            else
                            {
                                yearlist.Add(coursenodelist[i].ChildNodes[1].InnerText);
                            }
                            if (semelist.Contains(coursenodelist[i].ChildNodes[2].InnerText))
                            {
                                //continue;
                            }
                            else
                            {
                                semelist.Add(coursenodelist[i].ChildNodes[2].InnerText);
                            }

                            //courselist.Rows.Add(coursenodelist[i].Attributes[0].Value.ToString(),coursenodelist[i].ChildNodes[0].InnerText);

                        }
                    }
                    cbyear.SelectedValueChanged -= cbyear_SelectedValueChanged;
                    cbsemester.SelectedValueChanged -= cbsemester_SelectedValueChanged;
                    cbcourse.SelectedValueChanged -= cbcourse_SelectedValueChanged;
                    //cbrubric.SelectedValueChanged -= cbrubric_SelectedValueChanged;

                    cbyear.DataSource = yearlist;
                    cbsemester.DataSource = semelist;
                    //cbcourse.DataSource = courselist;
                    //cbcourse.DisplayMember = courselist.Columns[1].ToString() ;
                    //cbcourse.ValueMember = courselist.Columns[0].ToString();

                    cbyear.SelectedValueChanged += cbyear_SelectedValueChanged;
                    cbsemester.SelectedValueChanged += cbsemester_SelectedValueChanged;
                    //cbcourse.SelectedValueChanged -= cbcourse_SelectedValueChanged;
                }
                else
                {
                    MessageBox.Show("file empty!");
                }
            }
            else
            {
                MessageBox.Show("file not exist!");
            }
        }

        private void btn_user_Click(object sender, EventArgs e)
        {
            Formmanagement user = new Formmanagement();
            user.Show();
            this.Hide();
        }
    }
}
